﻿using Chloe.SqlServer;
using Kang.DbDoc.Abstracts;
using Kang.DbDoc.DatabaseHelper;
using Kang.DbDoc.Domains;
using Kang.DbDoc.Enums;
using Kang.DbDoc.Interfaces;
using Kang.DbDoc.Models;
using Kang.ExtendMethod;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Kang.DbDoc.ServicesImpl
{
    public class SqlServerConnectionService : BaseConnection, IConnectionService
    {
        public string GetConnectionString(DatabaseModel databaseModel)
        {
            var sqlServerBuilder = new SqlConnectionStringBuilder();
            sqlServerBuilder.DataSource = $"{databaseModel.DatabaseHost},{databaseModel.DatabasePort}";
            sqlServerBuilder.InitialCatalog = databaseModel.DatabaseName;
            sqlServerBuilder.UserID = databaseModel.DatabaseUsername;
            sqlServerBuilder.Password = databaseModel.DatabasePassword;
            sqlServerBuilder.ConnectTimeout = databaseModel.TimeOut;
            return sqlServerBuilder.ToString();
        }

        public void SetDefault(DatabaseModel databaseModel)
        {
            databaseModel.DatabasePort = 1433;
            databaseModel.DatabaseUsername = "sa";
            if (databaseModel.DatabaseHost.IsBlank())
            {
                databaseModel.DatabaseHost = "127.0.0.1";
            }
        }

        public void LoadDatabaseData(DatabaseModel databaseModel, string SearchWord)
        {
            string sql = @"
                SELECT
                    a.name AS 'table_name', 
                    b.value AS 'table_comment', 
                    a.xtype AS 'table_type'
                FROM        
                    sysobjects a
                LEFT JOIN
                    sys.extended_properties b ON (a.id = b.major_id AND b.minor_id = '0')
                WHERE         
                  a.status >= 0 AND (a.xtype = 'U' OR a.xtype = 'V') AND a.name LIKE '%" + SearchWord.StringTrim() + "%';";
            string sqlViewsInfo = @"
                SELECT
                	av.name,
                	asm.definition as 'view_definition'
                FROM
                	sys.all_views av
                	LEFT JOIN sys.all_sql_modules asm ON av.object_id = asm.object_id
                WHERE
                	av.schema_id = SCHEMA_ID( N'dbo' ) AND av.name LIKE '%" + SearchWord.StringTrim() + "%' " + @"
                ORDER BY
                	av.name ASC";
            string sqlColumns = @"
                SELECT
                    OBJECT_NAME(b.id) AS 'table_name',
                	b.name AS 'column_name', 
                    c.name AS 'column_type',
                    b.prec AS 'column_length',
                	b.scale AS 'column_scale',
                	CASE WHEN NOT f.id IS NULL THEN '1' ELSE '0' END 'column_key_type',
                	CASE WHEN COLUMNPROPERTY(b.id,b.NAME,'ISIDENTITY') = 1 THEN '1' ELSE '0' END AS 'column_extra',
                	b.isnullable AS 'column_null',
                	h.text AS 'column_default',
                	CONVERT(VARCHAR(1000),ISNULL(g.value,'')) AS 'column_comment'
                FROM  
                    sysobjects a
                INNER JOIN
                    syscolumns b ON a.id = b.id 
                INNER JOIN 
                    systypes c ON b.xtype = c.xusertype
                LEFT JOIN
                    sysobjects d ON (b.id = d.parent_obj AND d.xtype='PK')
                LEFT JOIN 
                    sysindexes e ON b.id = e.id AND d.name = e.name
                LEFT JOIN 
                    sysindexkeys f ON b.id = f.id AND b.colid=f.colid AND e.indid=f.indid
                LEFT JOIN
                    sys.extended_properties g ON (b.id = g.major_id AND b.colid = g.minor_id)
                LEFT JOIN
                    syscomments h ON (b.cdefault = h.id)
                WHERE 
                    a.status >= 0 AND (a.xtype = 'U' OR a.xtype = 'V') AND a.name LIKE '%" + SearchWord.StringTrim() + "%';";
            string sqlRoutine = @"
                SELECT
                	o.name AS 'routine_name',
                	o.type AS 'routine_type',
                	g.value AS 'routine_comment',
                	'' AS 'data_type',
                	m.definition AS 'routine_definition',
                	'' AS 'definer'
                FROM
                	sys.sql_modules m
                INNER JOIN 
                    sys.all_objects o ON m.object_id = o.object_id
                INNER JOIN 
                    sys.extended_properties g ON g.major_id = o.object_id AND g.minor_id = '0'
                WHERE
                	o.type = 'P' OR o.type = 'FN' AND a.name LIKE '%" + SearchWord.StringTrim() + "%';";
            using (var my = new MsSqlContext(new MsSqlConnectionFactory(databaseModel.ConnectionString)))
            {
                var tableList = my.SqlQuery<DbTable>($"{sql.StringTrim()}")
                .ToList();
                var viewList = my.SqlQuery<DbView>($"{sqlViewsInfo.StringTrim()}")
                .ToList();
                var columnList = my.SqlQuery<DbColumn>($"{sqlColumns.StringTrim()}")
                .ToList();
                tableList.ForEach(x =>
                {
                    var item = new TableOrViewModel()
                    {
                        Name = x.Name,
                        IsCheck = false,
                        Type = GetTableType(x.Type),
                        Comment = x.Comment,
                    };
                    var Columns = columnList.Where(a => a.TableName == x.Name).ToList();
                    foreach (var c in Columns)
                    {
                        item.Columns.Add(new ColumnModel()
                        {
                            Name = c.Name,
                            Comment = c.Comment,
                            Type = c.Type,
                            Length = c.ColumnLength,
                            Scale = c.ColumnScale,
                            KeyType = c.ColumnKeyType,
                            Extra = c.ColumnExtra,
                            IsAutoIncrement = c.ColumnExtra == "1",
                            IsNull = c.ColumnNull == "1",
                            Default = c.ColumnDefault
                        });
                    }
                    App.Current.Dispatcher.Invoke(() =>
                    {
                        switch (item.Type)
                        {
                            case TableTypeEnum.Table:
                                databaseModel.Tables.Add(item);
                                break;
                            case TableTypeEnum.View:
                                item.ViewDefiner = viewList.Where(v => v.Name == item.Name).Select(v => v.Definer).FirstOrDefault();
                                item.ViewDefinition = viewList.Where(v => v.Name == item.Name).Select(v => v.ViewDefinition).FirstOrDefault();
                                databaseModel.Views.Add(item);
                                break;
                        }
                    });
                });
                var routineList = my.SqlQuery<DbRoutine>($"{sqlRoutine.StringTrim()};")
                .ToList();
                routineList.ForEach(x =>
                {
                    var item = new RoutineModel()
                    {
                        Name = x.Name,
                        IsCheck = false,
                        Type = GetRoutineType(x.Type),
                        Comment = x.Comment,
                        DataType = x.DataType,
                        Definition = x.Definition,
                        Definer = x.Definer,
                    };
                    App.Current.Dispatcher.Invoke(() =>
                    {
                        switch (item.Type)
                        {
                            case RoutineTypeEnum.Function:
                                databaseModel.Functions.Add(item);
                                break;
                            case RoutineTypeEnum.Procedure:
                                databaseModel.Procedures.Add(item);
                                break;
                        }
                    });
                });
            }
        }
    }
}
